package vn.com.demand.util;

import java.io.File;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DecimalFormat;
import java.text.NumberFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;

import javax.sql.DataSource;

import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.eclipse.jface.dialogs.MessageDialog;

import vn.com.demand.lux.Activator;

import com.microsoft.jdbcx.sqlserver.SQLServerDataSource;

public class DpUtils {

	static SQLServerDataSource ds;
	static NumberFormat nf = new DecimalFormat("###,###,###.###");
	static SimpleDateFormat df = null;

	public static synchronized SimpleDateFormat getDateFormat() {
		if (null != df) {
			return df;
		}
		df = new SimpleDateFormat(trim(Activator.getDefault()
				.getDateFormatTypePreferences()));
		return df;
	}

	public synchronized static DataSource getDataSource() {
		if (ds != null) {
			return ds;
		}
		try {
			String[] strings = Activator.getDefault().getDatabaseProperties();
			if (strings.length <= 0) {
				strings = Activator.getDefault().getDefaultDatabaseProperties();
			}
			ds = new SQLServerDataSource();
			ds.setServerName(strings[0]);
			ds.setPortNumber(Integer.valueOf(strings[1]));
			ds.setDatabaseName(strings[2]);
			ds.setUser(strings[3]);
			ds.setPassword(strings[4]);
			return ds;
			
		} catch (NullPointerException e) {
			ds = new SQLServerDataSource();
			ds.setServerName("localhost");
			ds.setPortNumber(1433);
			ds.setDatabaseName("lux");
			ds.setUser("sa");
			ds.setPassword("sa");
			return ds;
		}
	}
	
	public static Connection getConnection() throws SQLException {
		return getDataSource().getConnection();
	}

	public static void releaseConnection(Connection conn) {
		DbUtils.closeQuietly(conn);
	}

	public static String trim(String s) {
		return s != null ? s.trim() : "";
	}
	
	public static void updateSequence(String table, int seqid) throws SQLException {
		String sql = "update demand_lux_sequence set seqid = ? where tablename = ?" ;
		QueryRunner runner = new QueryRunner(DpUtils.getDataSource());
		Object[] params = new Object[] {seqid, table};
		System.out.println(sql);
		runner.update(sql, params);
	}
	
	public static int genNextId(String table) throws SQLException {
		return genNextId(table, 1);
	}

	public static int genNextId(String table, int range) throws SQLException {
		Connection conn = getConnection();
		try {
			conn.setAutoCommit(false);
			conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
			int nextId = genNextId(conn, table, range);
			conn.commit();
			return nextId;
		} catch (SQLException e) {
			conn.rollback();
			throw e;
		} finally {
			releaseConnection(conn);
		}
	}

	public static int genNextId(Connection conn, String table)
			throws SQLException {
		return genNextId(conn, table, 1);
	}

	public static int genNextId(Connection conn, String table, int range)
			throws SQLException {
		String sql = "select seqid from demand_lux_sequence where tablename = ?";
		QueryRunner runner = new QueryRunner(DpUtils.getDataSource());
		int nextId = (Integer) runner.query(conn, sql, table,
				new ResultSetHandler() {

					public Object handle(ResultSet rs) throws SQLException {
						return rs.next() ? rs.getInt(1) : null;
					}

				});
		nextId += range;
		String update = "update demand_lux_sequence set seqid = ? where tablename = ?";
		runner.update(conn, update, new Object[] { nextId, table });
		return nextId;
	}

	

	public static String format(double value) {
		return nf.format(value);
	}

	public static String format(double value, String pattern) {
		nf = new DecimalFormat(pattern);
		return format(value, nf);
	}
	
	public static double round(double value, int scale, int roundingMode) {
		BigDecimal decimal = new BigDecimal(value);
		decimal = decimal.setScale(scale, roundingMode);
		value = decimal.doubleValue();
		return value;
	} 
	
	public static String format(double value, NumberFormat format) {
		if (null != format) {
			return format.format(value); 
		} else {
			return nf.format(value);
		}
	}
	
	public static Date parseDate(String value) {
		try {
			return df.parse(value);
		} catch (ParseException e) {
			e.printStackTrace();
		}
		return null;
	}
	
	public static Date parseDate(String value, String patern) {
		try {
			getDateFormat().applyLocalizedPattern(patern);
			return df.parse(value);
		} catch (ParseException e) {
			e.printStackTrace();
		}
		return null;
	}
	
	public static double parse(String value) {
		try {
			return (double) nf.parse(value).doubleValue();
		} catch (ParseException e) {
			e.printStackTrace();
		}
		return 0.0;
	}

	public static String toString(Object v) {
		if (v == null) {
			return null;
		}
		if (v instanceof String) {
			return trim(v.toString());
		}
		if (v instanceof Number) {
			return format(((Number) v).doubleValue());
		}
		if (v instanceof Date) {
			return getDateFormat().format(v);
		}
		return trim(v.toString());
	}

	public static String format(Date expectedFinishedDate) {
		getDateFormat().applyLocalizedPattern(Activator.getDefault().getDateFormatTypePreferences());
		return null != expectedFinishedDate ? getDateFormat().format(expectedFinishedDate)
				: "";
	}

	public static int length(String text) {
		return trim(text).length();
	}
	
	public static String format(Date expectedFinishedDate, String pattern) {
		getDateFormat().applyLocalizedPattern(pattern);
		return null != expectedFinishedDate ? getDateFormat().format(expectedFinishedDate)
				: "";
	}
	
	public static boolean isInteger(String string) {
		try {
			Integer.parseInt(string);
			return true;
		} catch (Exception e) {
			return false;
		}
	}
	
	public static boolean isDouble(String string) {
		try {
			Double.parseDouble(string);
			return true;
		} catch (Exception e) {
			MessageDialog.openError(null, "Error", "The input type must be a number");
			return false;
		}
		
	}
	
	public static java.sql.Date getSqlDate(Date d) {
		return d == null ? null : new java.sql.Date(d.getTime());
	}
	
	public static int getMonth(Date d) {
		Calendar cal = Calendar.getInstance();
		cal.setTime(d);
		return cal.get(Calendar.MONTH);
	}
	
	public static int getYear(Date d) {
		Calendar cal = Calendar.getInstance();
		cal.setTime(d);
		return cal.get(Calendar.YEAR);
	}
	
	public static int getDate(Date d) {
		Calendar cal = Calendar.getInstance();
		cal.setTime(d);
		return cal.get(Calendar.DATE);
	}
	
	public static Date makeDate(int year, int month, int date) {
		Calendar cal = Calendar.getInstance();
		cal.set(Calendar.YEAR, year);
		cal.set(Calendar.MONTH, month);
		cal.set(Calendar.DATE, date);
		return new Date(cal.getTimeInMillis());
	}

	public static String getTempFile(String pattern) {
	        String tmpPath = System.getenv("TMP");
	        if (tmpPath == null) {
	            tmpPath = "/tmp";
	        }
	        String path = tmpPath + File.separator + pattern + System.currentTimeMillis() + ".xml";
	        return path;
	    }

	public static void rollback(Connection conn) {
		DbUtils.rollbackAndCloseQuietly(conn);
	}
	
	public static String toArrayStringInDB(String[] items) {
		String str = "(";
		for (int i = 0; i < items.length; i++) {
			if(i == (items.length-1)) {
				str += "'" + items[i] + "'";
			} else {
				str += "'" + items[i] + "',";
			}
		}
		str += ")";
		return str;
	}
	
}
